package maturaarbeit.nicola_pfister.marks.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBAdapter {
	
	int id = 0;
	public static final String KEY_ROWID = "_id";
	public static final String KEY_DATE = "date";
	public static final String KEY_SUBJECT = "subject";
	public static final String KEY_VALUE = "value";
	public static final	String TAG = "DBAdapter";
	
	private static final String DATABASE_NAME = "database";
	private static final String DATABASE_TABLE_MARKS = "marks";
	private static final String DATABASE_TABLE_SUBJECTS = "subjects";
	private static final String DATABASE_TABLE_AVERAGE = "average";
	private static final int DATABASE_VERSION = 1;
	
	private static final String DATABASE_CREATE_MARKS =
			"create table " + DATABASE_TABLE_MARKS + " (" + KEY_ROWID + " integer primary key autoincrement, " +
					KEY_DATE +" text not null, " +
					KEY_SUBJECT +" text not null, " +
					KEY_VALUE +" text not null" +  ");";
	
	private static final String DATABASE_CREATE_SUBJECTS =
			"create table " + DATABASE_TABLE_SUBJECTS + " (" + KEY_ROWID + " integer primary key autoincrement, " +
					KEY_SUBJECT +" text not null " + ");";
	
	private static final String DATABASE_CREATE_AVERAGE =
			"create table " + DATABASE_TABLE_AVERAGE + " (" + KEY_ROWID + " integer primary key autoincrement, " +
					KEY_SUBJECT +" text not null, " +
					KEY_VALUE +" text not null " + ");";
	
	@SuppressWarnings("unused")
	private final Context context;
	
	private DatabaseHelper DBHelper;
	private SQLiteDatabase db;
	
	public DBAdapter(Context context) {
		this.context = context;
		DBHelper = new DatabaseHelper(context);
	}
	
	private static class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}
		
		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(DATABASE_CREATE_MARKS);
			db.execSQL(DATABASE_CREATE_SUBJECTS);
			db.execSQL(DATABASE_CREATE_AVERAGE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_MARKS);
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_SUBJECTS);
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_AVERAGE);
			onCreate(db);
		}
	}
	
	public DBAdapter open() throws SQLException {
		db = DBHelper.getWritableDatabase();
		return this;
	}
	
	public void close() {
		DBHelper.close();
	}
	
	public boolean clean () {
		db.delete(DATABASE_TABLE_SUBJECTS, KEY_SUBJECT +"=?", new String[] {""});
		db.delete(DATABASE_TABLE_MARKS, KEY_VALUE + "=?", new String[] {""});
		return true;
	}

	public long insertMark (String date, String subject, double value) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_DATE, date);
		initialValues.put(KEY_SUBJECT, subject);
		initialValues.put(KEY_VALUE, value);
		return db.insert(DATABASE_TABLE_MARKS, null, initialValues);
	}
	
	public Cursor getMark(long rowId) throws SQLException {
		Cursor mCursor =
				db.query(true, DATABASE_TABLE_MARKS, new String[] {
						KEY_ROWID,
						KEY_DATE,
						KEY_SUBJECT,
						KEY_VALUE
				},
				KEY_ROWID + "=" + rowId,
				null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	
	public Cursor getMarks(String[] subject) {
	    return db.query(true, DATABASE_TABLE_MARKS, new String[] {
	            KEY_ROWID,
	            KEY_DATE,
	            KEY_SUBJECT,
	            KEY_VALUE
	    }, KEY_SUBJECT + " = ?", subject, null, null, null, null);
	}
	
	public boolean updateMark(long rowId, String date, String subject, double value) {
		ContentValues args = new ContentValues();
		args.put(KEY_DATE, date);
		args.put(KEY_SUBJECT, subject);
		args.put(KEY_VALUE, value);
		return db.update(DATABASE_TABLE_MARKS, args, KEY_ROWID + "=" + rowId, null) >0;
	}

	public boolean deleteMark (long rowId) {
		db.delete(DATABASE_TABLE_MARKS, KEY_ROWID + "=" + rowId, null);
		return true;
	}
	
	public long insertSubject (String subject) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_SUBJECT, subject);
		return db.insert(DATABASE_TABLE_SUBJECTS, null, initialValues);
	}
	
	public Cursor getSubject(long rowId) throws SQLException {
		Cursor mCursor =
				db.query(true, DATABASE_TABLE_SUBJECTS, new String[] {
						KEY_ROWID,
						KEY_SUBJECT
				},
				KEY_ROWID + "=" + rowId,
				null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	public Cursor getAllSubjects() {
		return db.query(DATABASE_TABLE_SUBJECTS, new String[] {
				KEY_ROWID,
				KEY_SUBJECT
		}, null, null, null, null, null);
	}

	public boolean editSubject (String subject, String newname) {
		ContentValues args = new ContentValues();
		args.put(KEY_SUBJECT, newname);
		db.update(DATABASE_TABLE_SUBJECTS, args, KEY_SUBJECT + "=?", new String[] {subject});
		db.update(DATABASE_TABLE_MARKS, args, KEY_SUBJECT + "=?", new String[] {subject});
		db.update(DATABASE_TABLE_AVERAGE, args, KEY_SUBJECT + "=?", new String[] {subject});
		return true;	
	}
	
	public boolean deleteSubject (String subject) {
		db.delete(DATABASE_TABLE_SUBJECTS, KEY_SUBJECT +"=?", new String[] {subject});
		db.delete(DATABASE_TABLE_MARKS, KEY_SUBJECT + "=?", new String[] {subject});
		db.delete(DATABASE_TABLE_AVERAGE, KEY_SUBJECT + "=?", new String[] {subject});
		return true;
	}
	
	public long insertAverage (String subject, double value) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_SUBJECT, subject);
		initialValues.put(KEY_VALUE, value);
		return db.insert(DATABASE_TABLE_AVERAGE, null, initialValues);
	}
	
	public Cursor getAverage(String subject) throws SQLException {
		Cursor mCursor =
				db.query(true, DATABASE_TABLE_AVERAGE, new String[] {
						KEY_ROWID,
						KEY_SUBJECT,
						KEY_VALUE
				},
				KEY_SUBJECT + "=?",
				new String[] {subject}, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	public void editAverage(String subject,String newsubject, double value) {
		ContentValues args = new ContentValues();
		args.put(KEY_SUBJECT, newsubject);
		args.put(KEY_VALUE, value);
		db.update(DATABASE_TABLE_AVERAGE, args, KEY_SUBJECT + "=?", new String[] {subject});
	}

	public void updateAverage(String subject, double value) {
		ContentValues args = new ContentValues();
		args.put(KEY_SUBJECT, subject);
		args.put(KEY_VALUE, value);
		db.update(DATABASE_TABLE_AVERAGE, args, KEY_SUBJECT + "=?", new String[] {subject});
	}
}
